"""
根据路宽csv计算存在的道路路宽，同路名取众数、平均数
"""
import pandas as pd

# pucha_df = pd.read_excel('csvs/pucha.xlsx', header=None)  # 普查文件路径
# pucha_df = pucha_df[2:]

luopu_df = pd.read_csv('ded/xinnan_gbk_circle_dlv101_dila.csv', header=None, encoding="gbk")  # 路宽csv路径
save_dir = 'ded/result_xinnan_101_utf8.csv'  # 结果保存路径

road_names = luopu_df[1]
road_names = set(road_names)
road_names.remove('ROAD')
# 创建一个新的 DataFrame 来存储结果
result_df = pd.DataFrame(columns=['路线名称', '车行路面宽度(众数)', '车行路面宽度(平均值)', '选取的行号'])

for index, item in enumerate(road_names):

    road_name = item
    # 在 luopu_df 中找到对应的路名
    matched_rows = luopu_df[luopu_df.iloc[:, 1] == road_name]

    if matched_rows.empty:
        # 如果没有找到对应的路名，则填 0
        result_df.loc[len(result_df)] = [road_name, 0, 0, []]
    else:
        matched_rows.iloc[:, 3] = matched_rows.iloc[:, 3].apply(lambda x: x.split(','))
        matched_rows = matched_rows[matched_rows.iloc[:, 3].apply(len) < 5]  # 筛选linestring长度小于5
        matched_rows.iloc[:, 2] = matched_rows.iloc[:, 2].astype(int, errors='ignore')
        matched_rows = matched_rows[matched_rows.iloc[:, 2] <= 200]  # 筛选distance
        matched_rows = matched_rows[50 < matched_rows.iloc[:, 2]]
        pixel_nums = matched_rows.iloc[:, 4]  # 第五列是 pixel_num

        if pixel_nums.notnull().any():  # 判断是否全为空
            pixel_nums = pixel_nums[pixel_nums.notnull()]  # 去除空值
            pixel_nums = pixel_nums.astype(int, errors='ignore')
            pixel_nums = pixel_nums[pixel_nums <= 36]  # 筛选pixel_num的大小
            pixel_nums = pixel_nums[1 < pixel_nums]
            if pixel_nums.notnull().any():  # 众数
                mode_value = pixel_nums.value_counts().index[0]
            else:
                mode_value = 0
            count = len(pixel_nums)
            if count <= 2:
                if pixel_nums.max() - pixel_nums.min() > 6:
                    avg = pixel_nums.min()
                else:
                    avg = pixel_nums.mean()
            elif 3 <= count <= 7:
                pixel_nums = pixel_nums.sort_values()
                pixel_nums = pixel_nums.iloc[1:-1]  # 去掉最大最小值求平均
                avg = pixel_nums.mean()
            elif count >= 8:
                pixel_nums = pixel_nums.sort_values()
                a = len(pixel_nums)
                pixel_nums = pixel_nums.iloc[int(a/4):int(3*a/4)]  # 选取中间值求平均
                avg = pixel_nums.mean()
            select_rows = pixel_nums.index.tolist()
            select_rows = [select_row + 1 for select_row in select_rows]
            result_df.loc[len(result_df)] = [road_name, mode_value, avg, select_rows]
        else:
            result_df.loc[len(result_df)] = [road_name, 0, 0, []]


# 将结果保存到新的文件中
result_df.to_csv(save_dir, index=False, encoding="utf-8")
